    # -*- coding: utf-8 -*-
'''
| @author: olivia.dou
| Created on: 2019/11/1 14:03
| desc: 和任务相关的功能
'''


import random,requests,logging, os
import pandas as pd
import base.globalvars as glo
from .model import get_model_element_ids,get_model_element_ids_str,get_model_random_file_ids,get_model_id,get_model_matched_files,get_model_category_id,get_model_info
from .element import get_element_id
from .basedata import get_type_id,get_category_name
from .workflow import STEP_FIELDS_MAP
from util.db_util import execute_query
from util.collection_util import random_select_from_list
from util.date_util import get_current_datetime
from jsonpath import jsonpath


def __init():
    from base.get_config import get_header_file
    from base.getdata import get_header
    global url1,url2,header1,header2
    url1 = glo.get_value('url1')
    url2 = glo.get_value('url2')
    header1 = get_header(get_header_file(), "header1")
    header2 = get_header(get_header_file(), "header2")

def get_task_ids(**kwargs):
    """根据kwargs指定的条件获取任务id

    :param kwargs:
        | model_id: 模型id
        | task_type: 任务类型，整数
        | task_types：逗号分隔的任务类型字符串，如'0,1,2' 其中 0 标注，1 采样， 2 维护， 3 审核
        | total_count：任务的条目数
        | executor：执行人
        | status: 任务状态，整数
        | status_str: 逗号分隔的任务状态字符串，如'0,1,2'， 其中 0-待确认 1-待处理 2-处理中 3-已完成 4-取消 5-已转移
        | cru: 创建人
        | crt_range: 创建时间区间，元组形式，如("2020-05-18 00:00:00","2020-05-18 23:59:59"), 如无起始时间或无结束时间则为空字符串""
        | order: 排序方式，默认升序，desc 降序

    :return: 符合条件的任务id列表
    """
    condition = ""
    for kwarg in kwargs:
        if kwarg in ["model_id","element_ids","task_type","total_count","status"]:
            condition+=" and %s=%s"%(kwarg,kwargs[kwarg])
        if kwarg in ["executor","cru"]:
            condition += " and %s='%s'" % (kwarg, kwargs[kwarg])
    # if "model_id" in kwargs:
    #     condition+=" and model_id=%s"%kwargs['model_id']
    if "element_ids" in kwargs:
        condition += " and element_ids in (%s)"%kwargs['element_ids']
    # if "task_type" in kwargs:
    #     condition+=" and task_type=%s"%kwargs['task_type']
    if "task_types_str" in kwargs:
        condition+=" and task_type in (%s)"%kwargs['task_types_str']
    # if "total_count" in kwargs:
    #     condition += " and total_count=%s" % kwargs['total_count']
    # if "executor" in kwargs:
    #     condition += " and executor='%s'" % kwargs['executor']
    # if "status" in kwargs:
    #     condition += " and status=%s" % kwargs['status']
    if "status_str" in kwargs:
        condition += " and status in (%s)" % kwargs['status_str']
    # if "cru" in kwargs:
    #     condition += " and cru='%s'" % kwargs['cru']
    if "crt_range" in kwargs:
        crt_starttime = kwargs['crt_range'][0]
        crt_endtime = kwargs['crt_range'][1]
        if crt_starttime!="":
            condition += " and crt>='%s'"%crt_starttime
        if crt_endtime!="":
            condition += " and crt<='%s'" % crt_endtime
    if "order" in kwargs and kwargs['order']=="desc":
        condition += " order by id desc"

    if condition.startswith(" and"):
        condition=condition.replace("and","where",1) #替换第一个and

    sql = "select id from t_das_task" + condition
    res = execute_query(sql)
    return [row[0] for row in res]

def get_task_count(**kwargs):
    """获取满足指定条件的任务总数，用于Excel case

        :param kwargs: 同get_task_ids

        :return: 满足指定条件的任务数量
        """

    task_connt = get_task_ids(**kwargs)
    if task_connt != []:
        res_count = len(task_connt)#直接得到查询sql返回的任务列表ID的数量
    return  res_count



def get_one_task_id(**kwargs):
    """获取满足指定条件的一个任务id，用于Excel case(无法在获取任务id列表后指定下标)

    :param kwargs: 同get_task_ids

    :return: 满足指定条件的一个任务id
    """
    task_ids = get_task_ids(**kwargs)
    if task_ids !=[]:
        if "order" not in kwargs: #未指定顺序，则随机获取一个
            id_list =  random_select_from_list(task_ids,n=1)
            if id_list !=[]:
                task_id = id_list[0]
            else:
                task_id = -1
        else:
            task_id = task_ids[0]

    else:
        task_id = -1

    glo.set_value("task_id",task_id) #供Excel case使用
    return task_id

def get_auto_task_ids(**kwargs):
    """根据kwargs指定的条件获取autotask id

    :param kwargs:
        | model_id：模型id
        | auto_mode: 自动模式(0:全自动/1:半自动)
        | order: 排序方式，默认升序，desc 降序

    :return: 满足条件的autotask id
    """

    condition = ""
    if "model_id" in kwargs:
        condition+=" and model_ids in (%s)"%kwargs['model_id']
    if "auto_mode" in kwargs:
        condition += " and auto_mode=%s" % kwargs['auto_mode']
    if "order" in kwargs and kwargs['order']=="desc":
        condition += " order by id desc"

    if condition.startswith(" and"):
        condition=condition.replace("and","where",1) #替换第一个and

    sql = "select id from t_das_auto_task" + condition
    res = execute_query(sql)
    return [row[0] for row in res]

def get_one_auto_task_id(**kwargs):
    """根据kwargs指定的条件获取一个autotask（提取任务） id，用于Excel case(无法在获取任务id列表后指定下标)

    :param kwargs: 同get_auto_task_ids

    :return: 满足指定条件的一个autotask（提取任务） id
    """
    task_ids = get_auto_task_ids(**kwargs)
    if task_ids !=[]:
        if "order" not in kwargs:
            id_list =  random_select_from_list(task_ids,n=1)
            if id_list !=[]:
                return id_list[0]
        else:
            return task_ids[0]

    return ""

def get_auto_task_item_files(autotask_id):
    """获取autotask（提取任务）的条目文件

    :param autotask_id: autotask（提取任务） ID

    :return: 指定autotask（提取任务）的条目文件id列表
    """
    sql = "SELECT file_id FROM t_das_auto_task_item WHERE task_id=%s"%autotask_id
    res = execute_query(sql)
    return [row[0] for row in res]

def get_auto_task_status(autotask_id):
    """获取autotask（提取任务）的状态

    :param autotask_id: autotask（提取任务）id

    :return: 指定autotask（提取任务）的状态
    """
    sql ="SELECT status from t_das_auto_task WHERE id=%s"%autotask_id
    res = execute_query(sql)
    return res[0][0]


def get_task_file_ids(task_id,**kwargs):
    """获取任务条目对应的文件ID

    :param task_id: 任务id
    :param element_id: 元素ID
    :param label: 采样结果类型

    :return: 指定任务的条目文件id列表
    """
    condition = ""
    if "element_id" and "label" in kwargs: #采样浏览采样结果筛选文件

        condition += "and t2.element_id = %s "%kwargs["element_id"]
        condition += "and t2.label = %s "%kwargs["label"]
        sql = "select t1.file_id from t_das_task_item t1 inner join t_das_task_sample t2 on t1.id = t2.task_item_id where t1.task_id = %s %s "%(task_id,condition)
    else:#只通过任务ID筛选文件
        sql = "select file_id from t_das_task_item where task_id=%s"%task_id

    res = execute_query(sql)
    file_ids = [row[0] for row in res]
    if "file_ids" in kwargs:
        if kwargs['file_ids']=="str":
            file_ids=','.join(str(x) for x in file_ids) # fileids用字符串形式返回
    return file_ids


def get_tasks_file_ids(task_ids):
    """获取多任务条目对应的文件ID

    :param task_ids: 任务ids,list

    :return: 指定多任务的条目文件id列表
    """
    try:
        file_ids = []
        for task_id in task_ids:
            res = execute_query("select file_id from t_das_task_item where task_id=%s" % task_id)
            for i in range(len(res)):
                file_ids.append(res[i][0])
        return file_ids
    except Exception as e:
        logging.error("get_tasks_file_ids:%s" % e)
        return None


def get_task_item_ids(task_id, **kwargs):
    """获取指定任务下的item id列表

    :param task_id: 任务id
    :param kwargs:
        | order：排序方式，默认升序，desc为降序

    :return: 指定任务下的item id列表
    """
    condition = ""
    if "order" in kwargs and kwargs['order']=="desc":
        condition += " order by id desc"
    if "file_id" in kwargs:
        condition += " and file_id =%s" % kwargs['file_id']

    sql = "select id from t_das_task_item where task_id=%s %s"%(task_id, condition)
    res = execute_query(sql)
    item_ids =[row[0] for row in res]
    if "task_item_ids" in kwargs:
        if kwargs['task_item_ids']=="all":
            item_ids=','.join(str(x) for x in item_ids) # taskitemids用字符串形式返回
        elif kwargs['task_item_ids']=="one":
            item_ids=str(item_ids[kwargs['i']]) # 取某条taskitemid
    return item_ids

def get_one_task_item_id(task_id, **kwargs):
    """获取指定任务下指定条件的任务条目id

    :param task_id: 任务id
    :param kwargs: 同get_task_item_ids

    :return: 符合条件的任务条目id
    """
    task_item_ids = get_task_item_ids(task_id)
    task_ids = get_auto_task_ids(**kwargs)
    if task_ids !=[]:
        if "order" not in kwargs:
            id_list =  random_select_from_list(task_ids,n=1)
            if id_list !=[]:
                return id_list[0]
        else:
            return task_ids[0]

    return ""

def get_task_model_id(task_id):
    """获取任务的模型id

    :param task_id: 任务id

    :return: 指定任务的模型id
    """
    sql = "select model_id from t_das_task where id=%s"%task_id
    res = execute_query(sql)
    return res[0][0]

def create_task(model_id: object, element_ids_str: object, task_type: object, file_ids: object, executors: object, status: object) -> object:
    """创建任务（标注、采样、维护、审核）

    :param model_id: 模型id
    :param task_type: 任务id
    :param file_ids: 文件id列表
    :param executors: 执行人列表
    :param status: 状态，枚举值UnConfirm, Pending, Processing, Done, Canceled, Changed

    :return: 无
    """

    __init()
    url = url1 + "/api/v1/Task/task-with-item/executors"

    payload =    {
        "status": status,
        "taskType": task_type,
        #"confirm": confirm,
        "fileIds": file_ids,
        "groupId": "",
        "executors": executors,
        "totalCount": len(file_ids),
        "modelId": model_id,
        "elementIds": element_ids_str
    }
    res = requests.post(url,headers = header2,json=payload)
    assert res.status_code==200


def submit_mark_task_items(task_id,n=-1):
    """提交指定标注任务下指定数量条目的所有元素的标注

    :param task_id: 任务id
    :param n: 需要提交的任务条目数

    :return: 无
    """

    from base.get_config import get_header_file
    __init()
    #from .model import get_model_element_ids #解决循环导入
    # jutil = JsonUtil(get_header_file())
    # header_value = jutil.get_data("header2")
    # headers = eval_from_string(repr(header_value))
    # headers2 = get_header(get_header_file(),"header2")

    #查询任务下的所有task item id
    task_item_ids = get_task_item_ids(task_id)
    #查询任务的模型下的模型元素
    model_id = get_task_model_id(task_id)
    element_ids = get_model_element_ids(model_id=model_id)

    #url1 = glo.get_value("url1") + "/api/v1/TaskMark/add/task-item/status?modelId=%s"%model_id
    if n!=-1: #指定需要提交的任务条目数
        task_item_ids = random_select_from_list(task_item_ids, n)

    body_list = [{"TaskItemId":item_id,"ElementId":ele_id,"MasterMark":1,"SubMark":1} for ele_id in element_ids for item_id in task_item_ids]

    for body in body_list:
        requests.post(url1 + "/api/v1/TaskMark/add/task-item/status?modelId=%s"%model_id,headers = header2,json=body)


# def submit_sample_task(task_id, n=-1, status_mode=1):
#     """
#     提交task_id指定任务下所有任务条目的所有element
#     :param task_id:
#     :param n:
#     :param status_mode: 0:element提交status为1,2相间, 1:全部elment提交status=1, 2:全部element提交status=2
#     :return:
#     """
#     #from .model import get_model_element_ids #解决循环导入
#     headers = get_header(get_header_file(),"header2")
#
#     #查询任务下的所有task item id
#     task_item_ids = get_task_item_ids(task_id)
#
#     if n>len(task_item_ids):
#         assert 1==0,"指定的任务条目数超过总的任务条目数"
#
#     #查询任务的模型下的模型元素
#     model_id = get_task_model_id(task_id)
#     element_ids = get_model_element_ids(model_id=model_id)
#
#     if status_mode==1:
#         status_list=[1]*len(element_ids)
#     elif status_mode==2:
#         status_list=[2]*len(element_ids)
#     else:
#         status_list = [1,2] * (len(element_ids) // 2)
#         if len(element_ids) % 2!=0:
#             status_list.extend([1])
#
#     zipped_list = list(zip(element_ids,status_list))
#
#     url = glo.get_value("url1") + "/api/v1/TaskSample/add/task-item-status?modelId=%s"%model_id
#     if n!=-1: #指定需要提交的任务条目数
#         task_item_ids = random_select_from_list(task_item_ids,n)
#     body_list = [{"TaskItemId":item_id,"ElementId":ele_id,"Status":None,"Label":label} for (ele_id,status) in zipped_list for item_id in task_item_ids]
#
#     for body in body_list:
#         requests.post(url,headers = headers,json=body)
#
#     return

def cancel_tasks(model_name,task_type_str,status_str):
    """取消指定模型的所有处理中指定类型训练任务

    :param model_name: 模型名称
    :param task_type_str: 逗号分隔的任务类型字符串，0：标注任务，1：采样任务，2：维护任务，3：审核任务
    :param status_str: 逗号分隔的任务状态字符串，0：待确认，1：待处理，2：处理中，3：已完成， 4：已取消

    :return: 无
    """
    __init()

    model_id = get_model_id(model_name)
    task_ids = get_task_ids(task_types_str=task_type_str,status_str=status_str,model_id=model_id)

    #url1 = glo.get_value("url1")
    #headers1 = get_header(get_header_file(), "header1")
    for id in task_ids:
        url = url1 + "/api/v1/Task/cancel?id=%s&reason=test"%id
        res = requests.get(url,headers=header1)
        assert res.status_code==200


def get_extract_task_criteria():
    """随机获取一个合适的提取任务的日期，即符合配置文件中trained_models模型条件的文件数不超过10个，返回该日期发布的文件列表

    :return: 文件id列表
    """

    __init()
    model_name = random_select_from_list(glo.get_value("trained_models").split(','),1)[0]
    files = get_model_matched_files(model_name)
    files_str = ','.join([str(file) for file in files])
    # 根据获取到的文件id重新按publish_date分组
    sql = "SELECT publish_date,report_year,report_period,count(*) from t_das_file where id in (%s) group by publish_date,report_year,report_period " \
          "having count(*)>1 and count(*)<=10"%files_str
    res = execute_query(sql)
    result = random_select_from_list(list(res),1)[0]
    # 设置全局变量，提供Excel case使用
    glo.set_value("extract_task_date", str(result[0]))
    glo.set_value("extract_task_report_year", str(result[1]))
    glo.set_value("extract_task_report_period", result[2])
    model_id = get_model_id(model_name)
    glo.set_value("extract_task_model_id",model_id)

    if len(str(result[2]))<2: #补0
        fp='0'+str(result[2])
    else:
        fp = str(result[2])
    fyfp = str(result[1])+fp
    publish_date = str(result[0])
    files = get_model_matched_files(model_name,fyfp,fyfp,start_date=publish_date,end_date=publish_date)

    #return result[3] # number of files
    return files,str(result[0]),str(result[1]),result[2],model_id


# def create_extract_task(group_name, model_ids, element_ids, steps, start_fyfp, end_fyfp, start_date_str="", end_date_str="", file_ids=[]):
#     """创建提取任务
#
#     :param group_name: 模型分组名称
#     :param model_ids: 逗号分隔的模型id字符串
#     :param element_ids: 逗号分隔的元素id字符串
#     :param steps: 步骤列表 0：提取 1：结构化 2：结构化质检 3：标准化 4：标准化质检 5：模板计算 6：模板质检
#     :param start_fyfp: 起始财年财季，如'201803'
#     :param end_fyfp: 结束财年财季，如'201812'
#     :param start_date_str: 开始日期
#     :param end_date_str: 结束日期
#
#     :return: 创建提取过程接口的response
#     """
#
#     __init()
#     payload = {"groupName":group_name,
#                 "modelIds": ','.join([str(id) for id in model_ids]),
#                "steps": ','.join([str(step) for step in steps]),
#                "elementIds": ','.join([str(id) for id in element_ids]),
#                "startDate": (start_date_str + " 00:00:00") if start_date_str != "" else "",
#                "endDate": (end_date_str + " 23:59:59") if end_date_str != "" else "",
#                #"reportYear": report_year,
#                #"reportPeriod": report_period,
#                 "startReportYear":start_fyfp[:4],
#                 "startReportPeriod":start_fyfp[4:],
#                 "endReportYear":end_fyfp[:4],
#                 "endReportPeriod":end_fyfp[4:],
#                # DAS-3474，提取任务增加是否调用标准化算法参数
#                "stdCommon":True,
#                "stdAlgo":False
#                }
#
#     if file_ids!=[]:
#         payload['fileIds'] = ','.join([str(id) for id in file_ids])
#     res = requests.post(glo.get_value("url1")+"/api/v1/AutoTask",headers = header2,json=payload)
#     return res

def create_extract_task( model_names, element_names, steps, **kwargs):
    """创建提取任务

    :param model_names: 逗号分隔的模型名称字符串
    :param element_names: 逗号分隔的元素id字符串
    :param type_names: 逗号分隔的文件小类名称
    :param steps: 步骤列表 0：提取 1：结构化 2：结构化质检 3：标准化 4：标准化质检 5：模板计算 6：模板质检
    :kwargs:
    | extract_result_type: 提取结果：0有|1无|2不适用|3仅表头
    | file_ids: 文件id组成的列表
    | fyfp: 财年财季范围，如'201206,,201212'
    | group_name: 模型分组名称
    | publish_date_between: 发布日期区间，如'2022-04-25 00:00:00,2022-04-25 23:59:59'
    | std_common: 执行通用标准化 True/False
    | std_algo: 执行算法标准化 True/False
    | std_qc_status: 标准化质检结果， 0，质检结果正确；1，质检结果错误；2:不适用
    | std_qc_rule_ctrl: 是否元素级别标准化质检 0 否 1 是
    | type_names: 逗号分隔的文件小类名称
    | unextract: 未提取 True/False

    :return: 创建提取过程接口的response
    """

    __init()

    model_ids = ','.join([str(get_model_id(name)) for name in model_names.split(',')])
    element_ids = ','.join([str(get_element_id(model_names.split(',')[0], name)) for name in element_names.split(',')]) #只能在单模型时指定元素
    category_id = get_model_category_id(model_name=model_names.split(',')[0])#只能在单模型时指定小类
    type_ids = ','.join(
        [str(get_type_id(get_category_name(category_id), type_name)) for type_name in kwargs['type_names'].split(',')]) if 'type_names' in kwargs else ''

    start_fyfp = kwargs['fyfp'].split(',')[0] if 'fyfp' in kwargs else ""
    end_fyfp =  kwargs['fyfp'].split(',')[1] if 'fyfp' in kwargs else ""
    payload = {
                "elementIds": element_ids,
                "endDate": kwargs['publish_date_between'].split(',')[1] if 'publish_date_between' in kwargs else "",
                "endReportYear": end_fyfp[:4],
                "endReportPeriod": end_fyfp[4:],
                "extractResultType":kwargs['extract_result_type'] if 'extract_result_type' in kwargs else '',
                "fileIds": kwargs['file_ids'] if 'file_ids' in kwargs else '',
                "groupName": kwargs['group_name'] if 'group_name' in kwargs else "",
                "modelIds": model_ids,
                "steps": steps,
                "startDate": kwargs['publish_date_between'].split(',')[0] if 'publish_date_between' in kwargs else "",
                "startReportYear": start_fyfp[:4],
                "startReportPeriod": start_fyfp[4:],
                # DAS-3474，提取任务增加是否调用标准化算法参数
                "stdCommon": kwargs['std_common'] if 'std_common' in kwargs else False,
                "stdAlgo": kwargs['std_algo'] if 'std_algo' in kwargs else False,
                "stdQcStatus":kwargs['std_qc_status'] if 'std_qc_status' in kwargs else "",
                "stdQcRuleCtrl":kwargs['std_qc_rule_ctrl'] if 'std_qc_rule_ctrl' in kwargs else 0,
                "typeIds": type_ids,
                "unExtract": kwargs['unextract'] if 'unextract' in kwargs else False
                }


    res = requests.post(glo.get_value("url1")+"/api/v1/AutoTask",headers = header2,json=payload)
    return res

def confirm_auto_task(task_id):
    """确认提取任务

    :param task_id: 任务id

    :return: 确认提取任务接口的response
    """
    res = requests.get(glo.get_value("url1")+"/api/v1/AutoTask/active/%s"%task_id,headers=header1)
    return res


# def continue_extract_task(task_id, group_name, model_ids, element_ids, steps, start_fyfp, end_fyfp, start_date="", end_date=""):
#     """继续提取任务
#     | Todo: stdCommon和stdAlgo的参数化
#
#     :param task_id: 任务id
#     :param group_name: 模型分组
#     :param model_ids: 逗号分隔的模型id字符串
#     :param element_ids: 逗号分隔的元素id字符串
#     :param steps: 步骤列表 0：提取 1：结构化 2：结构化质检 3：标准化 4：标准化质检 5：模板计算 6：模板质检
#     :param start_fyfp: 起始财年财季，如201803
#     :param end_fyfp: 结束财年财季，如201812
#     :param start_date: 开始日期
#     :param end_date: 结束日期
#
#     :return: 继续提取任务接口的response
#     """
#
#     logging.info("continue task: %s"%task_id)
#     payload = {"groupName":group_name,
#                "modelIds": ','.join([str(id) for id in model_ids]),
#                "steps": ','.join([str(step) for step in steps]),
#                "elementIds": ','.join([str(id) for id in element_ids]),
#                "startDate": (start_date+" 00:00:00") if start_date!="" else "",
#                "endDate": (end_date + " 23:59:59") if end_date!="" else "",
#                "startReportYear":start_fyfp[:4],
#                "startReportPeriod":start_fyfp[4:],
#                "endReportYear":end_fyfp[:4],
#                "endReportPeriod":end_fyfp[4:],
#                "stdCommon": True,
#                "stdAlgo": True
#                }
#     res = requests.put(glo.get_value("url1")+"/api/v1/AutoTask/continue-task/%s"%task_id,headers = header2,json=payload)
#     return res


def continue_extract_task(task_id, model_names, element_names, steps, **kwargs):
    """继续提取任务

    :param task_id: 需要继续的提取任务id
    :param model_names: 逗号分隔的模型名称字符串
    :param element_names: 逗号分隔的元素id字符串
    :param steps: 步骤列表 0：提取 1：结构化 2：结构化质检 3：标准化 4：标准化质检 5：模板计算 6：模板质检
    :kwargs:
    | extract_result_type: 提取结果：0有|1无|2不适用|3仅表头
    | file_ids: 文件id组成的列表
    | fyfp: 财年财季范围，如'201206,,201212'
    | group_name: 模型分组名称
    | publish_date_between: 发布日期区间，如'2022-04-25 00:00:00,2022-04-25 23:59:59'
    | std_common: 执行通用标准化 True/False
    | std_algo: 执行算法标准化 True/False
    | std_qc_status: 标准化质检结果， 0，质检结果正确；1，质检结果错误；2:不适用
    | std_qc_rule_ctrl: 是否元素级别标准化质检 0 否 1 是
    | type_names: 逗号分隔的文件小类名称
    | unextract: 未提取 True/False
    """
    model_ids = ','.join([str(get_model_id(name)) for name in model_names.split(',')])
    element_ids = ','.join(
        [str(get_element_id(model_names.split(',')[0], name)) for name in element_names.split(',')])  # 只能在单模型时指定元素
    category_id = get_model_category_id(model_name=model_names.split(',')[0])  # 只能在单模型时指定小类
    type_ids = ','.join(
        [str(get_type_id(get_category_name(category_id), type_name)) for type_name in
         kwargs['type_names'].split(',')]) if 'type_names' in kwargs else ''

    start_fyfp = kwargs['fyfp'].split(',')[0] if 'fyfp' in kwargs else ""
    end_fyfp = kwargs['fyfp'].split(',')[1] if 'fyfp' in kwargs else ""

    logging.info("continue task: %s"%task_id)
    payload = {"elementIds": element_ids,
                "endDate": kwargs['publish_date_between'].split(',')[1] if 'publish_date_between' in kwargs else "",
                "endReportYear": end_fyfp[:4],
                "endReportPeriod": end_fyfp[4:],
                "extractResultType":kwargs['extract_result_type'] if 'extract_result_type' in kwargs else '',
                "fileIds": kwargs['file_ids'] if 'file_ids' in kwargs else "",
                "groupName": kwargs['group_name'] if 'group_name' in kwargs else "",
                "modelIds": model_ids,
                "steps": steps,
                "startDate": kwargs['publish_date_between'].split(',')[0] if 'publish_date_between' in kwargs else "",
                "startReportYear": start_fyfp[:4],
                "startReportPeriod": start_fyfp[4:],
                # DAS-3474，提取任务增加是否调用标准化算法参数
                "stdCommon": kwargs['std_common'] if 'std_common' in kwargs else False,
                "stdAlgo": kwargs['std_algo'] if 'std_algo' in kwargs else False,
                "stdQcStatus":kwargs['std_qc_status'] if 'std_qc_status' in kwargs else "",
                "stdQcRuleCtrl":kwargs['std_qc_rule_ctrl'] if 'std_qc_rule_ctrl' in kwargs else 0,
                "typeIds": type_ids,
                "unExtract": kwargs['unextract'] if 'unextract' in kwargs else False
               }
    res = requests.put(glo.get_value("url1")+"/api/v1/AutoTask/continue-task/%s"%task_id,headers = header2,json=payload)
    return res


def get_batch_id(task_id):
    """获取审核任务的batch_id

    :param task_id: 审核任务的id

    :return: 指定审核任务的batch_id
    """
    sql = """select id from t_das_std_review_batch where task_id=%s"""%task_id
    try:
        res = execute_query(sql)
        return res[0][0]
    except Exception as e:
        return -1

def create_review_task(model_name,type_names,start_fyfp, end_fyfp,executors,element_names_str="",**kwargs):
    """创建审核任务

    :param model_name: 模型名称
    :param type_names: 逗号分隔的小类名称
    :param start_fyfp: 开始财年财季，如201803
    :param end_fyfp: 结束财年财季，如201812
    :param executors: 执行人
    :param element_names_str: 逗号分隔的元素名称字符串
    :param kwargs:
        | std_qc_success： 标准化质检是否成功 True 成功
        | org_ids_str： 逗号分隔的org_id字符串
        | publish_date_between： 发布日期区间, 如2019-03-01 00:00:00,2019-04-30 23:59:59
        | std_review_status： 标准化审核状态，如["ReviewUnPass"]，["UnReview"]

    :return: 创建的审核任务id列表
    """

    __init()

    model_id = get_model_id(model_name)
    category_id = get_model_category_id(model_name=model_name)
    type_ids = ','.join([str(get_type_id(get_category_name(category_id), type_name)) for type_name in type_names.split(',')])
    #files = kwargs['files'] if 'files' in kwargs else []

    if element_names_str == "":
        element_ids = get_model_element_ids(model_name=model_name)
    else:
        element_names = element_names_str.split(',')
        element_ids = [get_element_id(model_name=model_name, element_name=name) for name in element_names]

    element_ids_str = ','.join([str(id) for id in element_ids])
    tag_ids = get_model_info(model_name=model_name)["tag_ids"]

    #if "std_qc_success" in kwargs and kwargs["std_qc_success"] is True: #质检成功方式创建审核任务：post data中指定文件


    payload = {
        # "groupId": "",
        "executors": executors,
        "stdQcSuccess": kwargs['std_qc_success'] if 'std_qc_success' in kwargs else "",
        "orgIds": kwargs['org_ids_str'] if 'org_ids_str' in kwargs else "",
        "categoryId": category_id,
        "typeIds": type_ids,
        "tagIds": tag_ids,
        "publishDateBetween": kwargs['publish_date_between'] if 'publish_date_between' in kwargs else "",
        # 格式：2019-04-30 00:00:00,2019-04-30 23:59:59
        "startReportYear": start_fyfp[:4],
        "startReportPeriod": start_fyfp[-2:],  # 需要去除heading 0?
        "endReportYear": end_fyfp[:4],
        "endReportPeriod": end_fyfp[-2:],
        "modelId": model_id,
        "elementIds": element_ids_str,
        "sortString": "",
        "stdReviewStatus":kwargs['std_review_status'] if 'std_review_status' in kwargs else ""
    }
    if "file_ids" in kwargs:# 质检成功方式创建审核任务：post data中指定文件
        payload['fileIds'] = kwargs['file_ids']
        payload['totalCount'] = len(kwargs['file_ids'])


    res = requests.post(url1 + '/api/v1/Task/review-task/executors', headers=header2, json=payload)
    # Todo: 调整验证条件？
    assert res.status_code==200, "创建审核任务失败：%s"%res.text
    if res.status_code==500:
        return []

    return res.json() #返回任务id的列表

def create_review_task_and_validate(model_name,type_name,start_fyfp, end_fyfp,executors,element_names_str="",**kwargs):
    """创建审核任务并验证批次表信息正确

    :param model_name:
    :param type_name: 逗号分隔的小类名称
    :param start_fyfp:
    :param end_fyfp:
    :param executors:
    :param element_names_str:
    :param kwargs:
    :param model_name: 模型名称
    :param type_name: 小类名称
    :param start_fyfp: 开始财年财季，如201803
    :param end_fyfp: 结束财年财季，如201812
    :param executors: 执行人
    :param element_names_str: 逗号分隔的元素名称字符串
    :param kwargs:
        | std_qc_success： 标准化质检是否成功 True 成功
        | org_ids_str： 逗号分隔的org_id字符串
        | publish_date_between： 发布日期区间, 如2019-03-01 00:00:00,2019-04-30 23:59:59
        | std_review_status： 标准化审核状态
    :return:
    """
    task_ids = create_review_task(model_name,type_name,start_fyfp, end_fyfp,executors,element_names_str,**kwargs)
    # if task_ids==[]: #创建任务时未找到文件
    #     target_file_elements=[]
    target_file_elements = []
    batch_ids = [get_batch_id(task_id) for task_id in task_ids] if task_ids!=[] else []

    model_id = get_model_id(model_name)
    category_id = get_model_category_id(model_name=model_name)
    # 小类为指定唯一
    type_id = get_type_id(get_category_name(category_id), type_name)

    if element_names_str == "":
        element_ids = get_model_element_ids(model_name=model_name)
    else:
        element_names = element_names_str.split(',')
        element_ids = [get_element_id(model_name=model_name, element_name=name) for name in element_names]

    element_ids_str = ','.join([str(id) for id in element_ids])
    # 针对创建审核任务，依照系统设计仅三种方式：审核失败、质检成功、按需选择。 质检成功和按需选择隐含未审核条件
    # 审核任务须指定一个小类
    if "org_ids_str" in kwargs and task_ids!=[]:
        expected_files = get_model_matched_files(model_name, start_fyfp=start_fyfp, end_fyfp=end_fyfp,
                                                 org_ids_str=kwargs['org_ids_str'], std_review_status=["UnReview"], element_ids_str=element_ids_str, type_ids_str=str(type_id))

        target_file_elements = get_elements_of_file_by_std_review_status(expected_files, element_ids,std_review_status=["UnReview"])


    #elif "std_review_status" in kwargs and kwargs['std_review_status'].lower()=="ReviewUnPass".lower() and task_ids!=[]:
    elif "std_review_status" in kwargs and task_ids != []:
        expected_files = get_model_matched_files(model_name, start_fyfp=start_fyfp, end_fyfp=end_fyfp,
                                                 std_review_status=kwargs['std_review_status'], element_ids_str=element_ids_str,type_ids_str=str(type_id))

        target_file_elements = get_elements_of_file_by_std_review_status(expected_files,element_ids,std_review_status=kwargs['std_review_status'])
    elif "std_qc_success" in kwargs and kwargs['std_qc_success'] is True:
        expected_files = get_model_matched_files(model_name, start_fyfp=start_fyfp, end_fyfp=end_fyfp,std_qc_success = True,
                                                 std_review_status=["UnReview","ReviewUnpass"], element_ids_str=element_ids_str,type_ids_str=str(type_id))

        target_file_elements = get_elements_of_file_by_std_review_status(expected_files, element_ids,
                                                                         std_review_status=["UnReview","ReviewUnpass"])

    if target_file_elements is None: # 结果为空列表为正常结果
        logging.error("获取目标文件元素失败")
        return False

    batch_details = []
    for batch_id in batch_ids:
        batch_detail = get_file_element_from_std_review_batch(batch_id)
        batch_details.extend(batch_detail)
    if list(set(target_file_elements).difference(set(batch_details)))==[]:
        return True
    else:
        logging.error("batch_detail与预期不一致，batch ids: %s, expected:%s, actual:%s"%(batch_ids, target_file_elements,batch_details))
        return False



def get_elements_of_file_by_std_review_status(file_ids, element_ids, std_review_status):
    """在指定元素范围内查找指定文件的指定审核条件的元素

    :param file_ids: 文件id的列表
    :param element_ids: 元素id的列表
    :param std_review_status: 标准化审核状态 UnReview-未审核，ReviewUnPass-审核失败

    :return: 文件,元素元组的列表
    """
    if file_ids==[]:
        logging.error("get_elements_of_file_by_std_review_status：文件列表为空")
        return None

    file_ids_str = ','.join([str(file) for file in file_ids])
    elements_str = ','.join([str(ele) for ele in element_ids])

    try:
        review_status = []
        if "UnReview" in std_review_status:
            review_status.append(0)
        if "ReviewPass" in std_review_status:
            review_status.append(1)
        if "ReviewUnPass" in std_review_status:
            review_status.append(2)
        review_status_str = "(%s)" % (','.join(str(status) for status in review_status))

        sql = """select file_id,element_id from t_das_std_review_status where file_id in (%s) and element_id in (%s)
                    and status in %s""" % (file_ids_str, elements_str,review_status_str)  # 标准化过的都会在review表里有记录

        res = execute_query(sql)
        return res

    except Exception as e:
        logging.error(e)
        return None


def get_file_element_from_std_review_batch(batch_id):
    """根据batch_id获取file_id,element_id的组合

    :param batch_id: 标准化审核批次id

    :return: 指定batch_id的文件,元素信息（元组的列表）
    """

    sql = """select file_id,element_id from t_das_std_review_batch_detail where batch_id=%s"""%batch_id
    try:
        res = execute_query(sql)
        return res
    except Exception as e:
        logging.error(e)
        return []

# #18479，97,12001
# def create_extract_task(group_name,model_ids_str,element_ids_str,initial_steps,fyfq_range):
#     fyfqs = fyfq_range.split(',')
#     #start_fy =
#     res = create_extract_process(group_name, model_ids_str, element_ids_str, initial_steps,
#                                  glo.get_value("extract_task_date") + " 00:00:00",
#                                  glo.get_value("extract_task_date") + " 23:59:59",
#                                  glo.get_value("extract_task_report_year"),
#                                  glo.get_value("extract_task_report_period"))
#     assert res.status_code == 200,"创建提取任务失败：%s"%res.text
#
#     task_id = res.text
#
#
#     # 确认任务
#     confirm_auto_task(task_id)
#     return task_id

def get_taskbad_id(model_name,file_id,task_id):
    """获取指定模型、文件、任务id的坏样id

    :param model_name: 模型名称
    :param file_id: 文件id
    :param task_id: 任务id

    :return: 坏样id
    """
    results = execute_query("SELECT id FROM t_das_task_sample_bad WHERE model_id=(SELECT id FROM t_das_model WHERE model_name='%s') AND file_id=%d AND task_id=%d" % (
    model_name, file_id, task_id))
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None

def get_taskitem_id(task_id, file_id):
    """获取指定任务、文件的任务条目id

    :param task_id: 任务id
    :param file_id: 文件id

    :return: 任务条目id
    """
    results = execute_query(
            "SELECT id FROM `t_das_task_item` WHERE task_id = %d AND file_id = %d" % (
                task_id, file_id))
    try:
        result = results[0][0]
        return result
    except Exception as e:
        logging.error("no result retrieved")
        return None


def execute_sample_task(task_id):
    """执行客户端采样任务操作，将所有task_item的所有元素的采样结果都标记完
    Todo: 增加一个labels参数，用于指定任务中各item各元素的label，如未提供则随机label

    :param task_id: 任务id

    :return: 采样标记列表 0-提取成功 1-提取成功无数据 2-不含相关元素 3-提取错误 4-结构化错误
    """

    task_item_ids = get_task_item_ids(task_id)
    model_id = get_task_model_id(task_id)
    element_ids = get_model_element_ids(model_id=model_id)

    url = url1 + "/api/v1/TaskSample/add/task-item-status?modelId=%s"%model_id
    labels = []
    for item_id in task_item_ids:
        for element_id in element_ids:
            sample_result = random.sample(range(6),1)
            if len(sample_result)>0:
                label = sample_result[0] #从0~5的序列中采样
                labels.append(label)
                payload = {"TaskItemId":item_id,
                            "ElementId":element_id,
                            "Status":None,
                            "Label":label
                            }
                res = requests.post(url,headers=header2,json=payload)
                assert res.status_code==200,"标记采样结果失败：%s"%res.text
                # 注：正常响应返回结果为true或false，当task item中所有元素均标记完时返回true,否则返回false

    return labels


def submit_task(task_id):
    """提交任务

    :param task_id: 任务id

    :return: 无
    """
    url = url1+"/api/v1/Task/%s"%task_id

    payload = {"ModelId":None,
                "TaskType":None,
                "TotalCount":None,
                "StartTime":None,
                "EndTime":None,
                "Executor":None,
                "Status":3,
                "Description":None,
                "GroupId":None}

    res = requests.put(url,headers = header2,json=payload)
    assert res.status_code==200,"提交任务失败： task id %s"%task_id

def sample_model(model_name,executors,**kwargs):
    """执行模型采样，重新训练的模型，或修改了核心元素的模型，结束训练前必须重新采样

    :param model_name: 模型名称
    :param executors: 执行人
    :param kwargs:
        | file_ids： 文件id的列表
        | n_files: 指定文件的个数

    :return: 采样的label列表
    """

    model_id = get_model_id(model_name)
    element_ids_str = get_model_element_ids_str(model_name = model_name)
    if 'file_ids' in kwargs: #指定采样的样本文件
        file_ids = kwargs['file_ids']
    elif 'n_files' in kwargs: #指定采样的文件数量，随机选择文件
        n_files = kwargs['n_files']
        file_ids = get_model_random_file_ids(model_name,n_files,**kwargs)
    else:
        assert 1==0,"Either files_ids or n_files should be specified!"

    #create_sample_task(model_id,file_ids,executors)
    create_task(model_id,element_ids_str,"Sampling",file_ids,executors,"Processing")

    task_labels = [] #采样任务对应的采样标记列表
    for executor in executors:
        task_id = get_one_task_id(task_type_str='1',status_str='2',executor=executor,order="desc")
        labels = execute_sample_task(task_id)
        submit_task(task_id)
        task_labels.append({task_id:labels})

    return task_labels

def get_tasksum(starttime: object, endtime: object, task_type: object, executor: object, status: object) -> object:

    sql = "SELECT id FROM `t_das_task` WHERE crt BETWEEN '%s'  AND '%s' AND task_type=%d AND executor='%s' AND `status`=%d"%(starttime,endtime,task_type,executor,status)
    res = execute_query(sql)
    return len(res)


def get_baseline_group_count(model_id=-1):
    """获取基线采样组的个数（验证基线采样列表接口返回的total数）

    :return: 基线采样组的个数
    """
    if model_id!=-1:
        model_substr = " and model_id=%s"%model_id
    else:
        model_substr = ""

    try:
        sql = """SELECT count(DISTINCT baseline_group) FROM t_das_task WHERE task_type IN (4,5) %s"""%model_substr

        res = execute_query(sql)

        return res[0][0]
    except Exception as e:
        logging.error("get_baseline_group_count： %s")
        return -1


def get_baseline_group_id(model_name, element_names_str=''):
    """获取模型（/+元素）的基线采样baseline group id

    :param model_name: 模型名称
    :param element_names_str: 元素名称

    :return: 对应的baseline group id
    """
    model_id = get_model_id(model_name)
    if element_names_str!= '':
        element_names = element_names_str.split(',')
        element_ids = [get_element_id(model_name, name) for name in element_names]
        element_ids_str = ','.join([str(id) for id in element_ids])
    else:
        element_ids_str = get_model_element_ids_str(model_name=model_name)

    element_filter = " where "
    sql = """select baseline_group from (select DISTINCT model_id, substring_index(substring_index(ifnull(t.element_ids,''),',',b.id),',',-1) as element,
    baseline_group, t.crt as crt from t_das_task t
                inner JOIN t_das_dict_currency b
                on b.id<=length(ifnull(t.element_ids,''))-length(replace(ifnull(t.element_ids,''),',',''))+1 where t.task_type in (4,5)) as a
                where a.element in (%s) order by crt desc""" % element_ids_str
    #res = execute_query(sql,result_type="dict")
    #return [row['baseline_group'] for row in res]
    res = execute_query(sql)
    return res[0][0]


def confirm_task(task_id):
    """任务确认

    :param task_id: 任务ID

    :return: True或者False
    """
    __init()
    url = url1 + "/api/v1/Task/put/%s" % task_id
    payload = {"status": 1}
    header = header2
    try:
        res = requests.post(url=url, headers=header, json=payload)
        assert res.status_code == 200, "任务确认接口响应状态码为%s：%s" % (res.status_code, res.text)
        return res.json()
    except Exception as e:
        logging.error("confirm_task：%s" % e)


def begin_task(task_id):
    """任务开始

    :param task_id: 任务ID

    :return: True或者False
    """
    __init()
    url = url1 + "/api/v1/Task/%s" % task_id
    header = header2
    payload = {"Status": 2}
    try:
        res = requests.put(url=url, headers=header, json=payload)
        assert res.status_code == 200, "任务开始接口响应状态码为%s：%s" % (res.status_code, res.text)
        return res.json()
    except Exception as e:
        logging.error("begin_task:%s" % e)


def add_sample_bad(task_id, file_id, model_id, element_id, remark="test"):
    """新增坏样收藏

    :param task_id: 采样任务ID
    :param model_id: 模型ID
    :param file_id: 文件ID
    :param remark: 坏样备注

    :return: True或者False
    """
    __init()
    header = header2
    url = url1+"/api/v1/TaskSampleBad"
    payload = {"fileId": file_id,
               "modelId": model_id,
               "elementId": element_id,
               "remark": remark,
               "taskId": task_id}
    try:
        res = requests.post(url=url, headers=header, json=payload)
        assert res.status_code == 200, "新增坏样收藏接口响应状态码为%s：%s" % (res.status_code, res.text)
        return res.json()
    except Exception as e:
        logging.error("add_sample_bad:%s" % e)


def ignore_mark(task_item_id, element_id):
    """忽略该任务下的模型元素对应的标注

    :param task_item_id: item_id
    :param element_id: 元素ID

    :return: True或者False
    """
    __init()
    url = url1 + "/api/v1/TaskMark/add/task-item/status"
    header = header2
    payload = {"TaskItemId": task_item_id,
               "ElementId": element_id}
    try:
        res = requests.post(url=url, headers=header, json=payload)
        assert res.status_code == 200, "忽略该任务下的模型元素对应的标注接口响应状态码为%s：%s" % (res.status_code, res.text)
        return res.json()
    except Exception as e:
        logging.error("ignore_mark:%s" % e)


def finish_task(task_id):
    """完成任务

    :param task_id: 任务ID

    :return: 无
    """
    __init()
    url = url1 + "/api/v1/Task/%s" % task_id
    header = header2
    payload = {"Status": 3}
    try:
        res = requests.put(url=url, headers=header, json=payload)
        assert res.status_code == 200, "完成任务接口响应状态码为%s：%s" % (res.status_code, res.text)
        return 1
    except Exception as e:
        logging.error("finish_task:%s" % e)
        return 0


def mark_sample(taskitem_id, element_id, label):
    """对抽样结果进行标记

    :param taskitem_id: ITEMID
    :param element_id: 元素ID
    :param label: 标记类型

    :return: 无
    """
    __init()
    url = url1 + "/api/v1/TaskSample/add/task-item-status"
    header = header2
    payload = {"TaskItemId": taskitem_id,
               "ElementId": element_id,
               "Label": label}
    try:
        res = requests.post(url=url, headers=header, json=payload)
        assert res.status_code == 200, "抽样结果接口响应状态码为%s：%s" % (res.status_code, res.text)
        return 1
    except Exception as e:
        logging.error("mark_sample:%s" % e)
        return 0


def get_sample_task_result(task_id, sample_label, element_id, model_id, i):
    """根据采样结果进行查询

    :param task_id: 任务ID
    :param sample_label: 标记类型（0,1,2,3,4）
    :param element_id: 元素ID
    :param model_id: 模型ID
    :param i: 循环参数

    :return: 无
    """
    __init()
    url = url2 + "/api/v1/ex/TaskItemEx/filter/page/2"
    header = header1
    params = {"taskId": task_id,
              "sampleLabel": sample_label,
              "ElementId": element_id,
              "ModelId": model_id}
    try:
        res = requests.get(url=url, headers=header, params=params)
        assert res.json()["total"] == i+1
        assert res.json()["data"][0]["file"]["id"] in [227342, 227367], "根据抽样结果进行查询接口响应状态码为%s：%s" % (res.status_code, res.text)
        return 1
    except Exception as e:
        logging.error("get_sample_task_result:%s" % e)
        return 0

def create_batch_review_task(start_fyfp, end_fyfp, publish_date_between,**kwargs):
    """
    :param model_ids: 逗号分隔的模型id字符串
    :param start_fyfp: 起始财年财季，如'201803'
    :param end_fyfp: 结束财年财季，如'201812'
    :param kwargs:


    :return:批量审核任务的id
    """
    __init()
    url = url1 +  "/api/v1/StdReviewBatch/save"
    payload = {
        "stdQcSuccess": kwargs['std_qc_success'] if 'std_qc_success' in kwargs else None,
        "modelId": "",
        "typeIds": kwargs['type_ids'] if 'type_ids' in kwargs else '',
        "tagIds": None,
        "startReportYear": start_fyfp[:4],
        "startReportPeriod": start_fyfp[4:],
        "endReportYear": end_fyfp[:4],
        "endReportPeriod": end_fyfp[4:],
        "publishDateBetween": kwargs['publish_date_between'] if 'publish_date_between' in kwargs else '',
        "elementIds": kwargs['element_ids'] if 'element_ids' in kwargs else '',
        "fileIds": kwargs['file_ids'] if 'file_ids' in kwargs else [],
        "orgOwner": kwargs['org_owner'] if 'org_owner' in kwargs else '',
        "financialPhase": kwargs['financial_phase'] if 'financial_phase' in kwargs else None,
        "modelGroup": kwargs['model_group'] if 'model_group' in kwargs else '',
        "modelIds": kwargs['model_ids'] if 'model_ids' in kwargs else ''
    }

    res = requests.post(url, headers=header2, json=payload)
    assert res.status_code==200,"创建批量审核任务失败：%s,%s"%(res.text,payload)
    return res.text


def batch_set_file_elements(table_name, status_datafrm, field_name ='status', **kwargs):
    """批量设置文件元素对应表的状态（审核任务、批量审核用例使用）

    :param table_name: file_element_datafrm中的status所在的表名
    :param status_datafrm: 包含file_id, model_id, element_id, status的DataFrame

    :return:
    """
    insert_sql = "INSERT INTO %s VALUES "%table_name
    update_sql = "UPDATE %s SET %s= CASE "%(table_name,field_name)
    delete_sql = "DELETE FROM %s WHERE "%table_name

    if 'stage' in kwargs:
        extra_filter = " AND stage='%s'"%kwargs['stage']
    else:
        extra_filter = ""


    for i,row in status_datafrm.iterrows():
        if row['status']==-1:
            delete_sql += "(file_id=%s AND model_id=%s AND element_id=%s %s) OR "%(row['file_id'], row['model_id'], row['element_id'], extra_filter)
        else:
            # 先确保有记录再更新
            query = "SELECT %s from %s WHERE file_id=%s AND model_id=%s AND element_id=%s"%\
                    (field_name, table_name, row['file_id'], row['model_id'], row['element_id'])
            try:
                res = execute_query(query)
            except Exception as e:
                logging.error(e)
                logging.error("sql: %s"%query)
                raise e

            if len(res)==0: #插入记录
                qc_group = get_model_info(model_id=row['model_id'])['qcgroup']
                if table_name=="t_das_std_qc_element_result":
                    insert_sql += "(null, 'test_batch',%s,%s,%s,%s,'', %s, '','','Automation', '%s', 'Automation', '%s'), " \
                                                   % (row['file_id'], qc_group, row['model_id'], row['element_id'],row['status'],
                                                      get_current_datetime(), get_current_datetime())
                elif table_name=="t_das_qc_review_status":
                    insert_sql += "(null, %s, %s, %s, %s, '', '%s', '', 'Automation', '%s', 'Automation', '%s'), " \
                                                   % (row['file_id'], row['model_id'], row['element_id'], row['status'],
                                                get_current_datetime(), get_current_datetime(), get_current_datetime())
                elif table_name == "t_das_std_review_status":
                    insert_sql += "(null, %s, %s, %s, 2, %s, '', 'Automation', '%s', 'Automation', '%s', '%s', 'Automation'), " \
                        % (row['model_id'], row['element_id'], row['file_id'], row['status'],
                           get_current_datetime(), get_current_datetime(), get_current_datetime())
                elif table_name == "t_das_outer_qc_result":
                    insert_sql += "(null, 'test_batch', %s, %s, %s, null, null, %s, 'test', '%s', 'Automation', '%s', 'Automation', null, null), " \
                                             %(row['file_id'], row['model_id'], row['element_id'], row['status'],get_current_datetime(),get_current_datetime())
                elif table_name ==  "t_das_dashboard_phase1_detail":
                    insert_sql += "(null, %s, %s, %s, '%s', %s, '', 'Automation', '%s', 'Automation', '%s'), " \
                                             % (row['file_id'], row['model_id'], row['element_id'], kwargs['stage'],
                                                row['status'], get_current_datetime(), get_current_datetime())
                # "t_das_extract_data"表没有无数据的情况，因此没有插入需求


                # insert_sql_map = {
                #     "t_das_std_qc_element_result": "(null, 'test_batch',%s,%s,%s,%s,'', %s, '','','Automation', '%s', 'Automation', '%s'), " \
                #                                    % (row['file_id'], qc_group, row['model_id'], row['element_id'],row['status'],
                #                                       get_current_datetime(), get_current_datetime()),
                #     "t_das_qc_review_status": "(null, %s, %s, %s, %s, '', '%s', '', 'Automation', '%s', 'Automation', '%s'), " \
                #                                    % (row['file_id'], row['model_id'], row['element_id'], row['status'],
                #                                 get_current_datetime(), get_current_datetime(), get_current_datetime()),
                #     "t_das_std_review_status": "(null, %s, %s, %s, 2, %s, '', 'Automation', '%s', 'Automation', '%s', '%s', 'Automation'), "
                #         % (row['model_id'], row['element_id'], row['file_id'], row['status'],
                #            get_current_datetime(), get_current_datetime(), get_current_datetime()),
                #     "t_das_outer_qc_result": "(null, 'test_batch', %s, %s, %s, null, null, %s, 'test', '%s', 'Automation', '%s', 'Automation', null, null), "
                #                              %(row['file_id'], row['model_id'], row['element_id'], row['status'],get_current_datetime(),get_current_datetime()),
                #     "t_das_dashboard_phase1_detail": "(null, %s, %s, %s, '%s', %s, '', 'Automation', '%s', 'Automation', '%s'), "
                #                              % (row['file_id'], row['model_id'], row['element_id'], kwargs['stage'] if 'stage' in kwargs else '',
                #                                 row['status'], get_current_datetime(), get_current_datetime())
                # }
                # insert_sql += insert_sql_map[table_name]
            elif res[0][0]==row['status']:#无需更新
                continue
            else:
                update_sql += " WHEN file_id=%s AND model_id=%s AND element_id=%s %s THEN %s"%\
                              (row['file_id'], row['model_id'], row['element_id'], extra_filter, row['status'])
            # sql = "UPDATE std_qc_element_status SET status=%s WHERE file_id=%s model_id=%s and element_id=%s"%\
            #       (row['std_qc_status'], row['file_id'], row['model_id'], row['element_id'])
    insert_sql = insert_sql.rstrip(", ")
    delete_sql = delete_sql.rstrip(" OR")
    update_sql += " ELSE %s END WHERE file_id in (%s)"%(field_name, ','.join([str(id) for id in list(set(status_datafrm['file_id']))]))

    try:
        if ',' in insert_sql:
            execute_query(insert_sql)
        if 'file_id' in delete_sql:
            execute_query(delete_sql)
        if 'WHEN' in update_sql:
            execute_query(update_sql)
        execute_query("commit;")
    except Exception as e:
        logging.error(e)




def set_up_file_elements(data_frm):
    """根据DataFrame的内容设置文件元素对应表的状态

    :param data_frm:
    :return:
    """

    # #real_path = os.path.split(os.path.realpath(__file__))[0]
    # # 读取测试数据
    # data_frm = pd.read_excel(file_path, sheet_name=case_id)
    # 设置文件元素状态
    if 'model_id' not in data_frm.columns:
        data_frm['model_id'] = data_frm[['model_name']].apply(lambda x: get_model_id(x['model_name']), axis=1)
    if 'element_id' not in data_frm.columns:
        data_frm['element_id'] = data_frm[['model_name', 'element_name']].apply(lambda x: get_element_id(x['model_name'], x['element_name']), axis=1)
    #file_element_datafrm = data_frm[['file_id', 'model_id', 'element_id']]
    column_names = data_frm.columns
    fields = set(column_names).difference({'file_id','model_name','element_name','model_id','element_id','selected'})
    for field in fields:
        status_datafrm = data_frm[['file_id', 'model_id', 'element_id']]
        status_datafrm['status'] = data_frm[field]
        if field=='dashboard_phase1_detail':
            batch_set_file_elements('t_das_%s' % field, status_datafrm, stage='Extract') #目前phase1 detail表仅针对提取状态设置有需求，若后续有其他需求再调整
        elif field=='extract_data': #目前针对t_das_extract_data表的设置需求仅针对result_type字段，若后续有其他需求再调整
            batch_set_file_elements('t_das_%s' % field, status_datafrm, field_name='result_type')
        else:
            batch_set_file_elements('t_das_%s' % field, status_datafrm)
    # std_qc_datafrm = data_frm[['file_id', 'model_id', 'element_id']]
    # std_qc_datafrm['status'] = data_frm['std_qc_status']
    # qc_review_datafrm = data_frm[['file_id', 'model_id', 'element_id']]
    # qc_review_datafrm['status'] = data_frm['qc_review_status']
    # std_review_datafrm = data_frm[['file_id', 'model_id', 'element_id']]
    # std_review_datafrm['status'] = data_frm['std_review_status']
    # # std_qc_datafrm.apply(lambda x: set_std_qc_status(x['file_id'],x['model_id'],x['element_id'],x['std_qc_status']))
    # batch_set_status('t_das_std_qc_element_result', std_qc_datafrm)
    # batch_set_status('t_das_qc_review_status', qc_review_datafrm)
    # batch_set_status('t_das_std_review_status', std_review_datafrm)

def create_maintain_task(model_name, type_names, start_fy_fp,end_fy_fp, executors, element_names_str='',**kwargs):
    """

    :param model_name: 模型名称
    :param type_names: 逗号分隔的小类名称
    :param start_fyfp: 开始财年财季，如201803
    :param end_fyfp: 结束财年财季，如201812
    :param executors: 执行人
    :param element_names_str: 逗号分隔的元素名称字符串
    :param kwargs:
        | std_qc_success： 标准化质检是否成功 True 成功
        | qc_result_status: 10 表示标准化质检不成功
        | org_ids_str： 逗号分隔的org_id字符串
        | publish_date_between： 发布日期区间, 如2019-03-01 00:00:00,2019-04-30 23:59:59
        | std_review_status： 标准化审核状态列表，如["ReviewUnPass"]

    :return:
    """
    __init()
    model_id = get_model_id(model_name)
    category_id = get_model_category_id(model_name=model_name)
    type_ids_str = ','.join(
        [str(get_type_id(get_category_name(category_id), type_name)) for type_name in type_names.split(',')])
    #files = kwargs['files'] if 'files' in kwargs else []

    if element_names_str == "":
        element_ids = get_model_element_ids(model_name=model_name)
    else:
        element_names = element_names_str.split(',')
        element_ids = [get_element_id(model_name=model_name, element_name=name) for name in element_names]

    element_ids_str = ','.join([str(id) for id in element_ids])
    tag_ids_str = get_model_info(model_name=model_name)["tag_ids"]

    url = url1 + "/api/v1/Task/maintain-task/executors"
    payload = {
            #"groupId": 1647744560148,
            "modelId": model_id,
            "executors": executors,
            "categoryId": 11,
            "typeIds": type_ids_str,
            "elementIds": element_ids_str,
            "publishDateBetween": kwargs['publish_date_between'] if 'publish_date_between' in kwargs else "",
            "tagIds": tag_ids_str,
            "startReportYear": start_fy_fp[:4],
            "startReportPeriod": start_fy_fp[4:],
            "endReportYear": end_fy_fp[:4],
            "endReportPeriod": end_fy_fp[4:]
    }
    if "std_review_status" in kwargs:
        payload["stdReviewStatus"] = kwargs['std_review_status']
    if "std_qc_success" in kwargs:
        payload['stdQcSuccess'] = kwargs['std_qc_success']
    if "qc_result_status" in kwargs:
        payload['qcResultStatus'] = kwargs['qc_result_status']
    if 'org_ids_str' in kwargs:
        payload['orgIds'] = kwargs['org_ids_str']


    res = requests.post(url, headers = header2, json = payload)
    assert res.status_code==200, "创建维护任务失败, %s"%res.text
    return res.json()


def get_std_review_batch_detail(**kwargs):
    """

    :param kwargs:
        | task_id: 审核任务的task id
        | batch_id: 批量审核任务的batch id

    :return:
    """
    __init()
    url = url1 + "/api/v1/StdReviewBatchDetail/filter/paging?pageIndex=0&pageSize=10000"
    if 'task_id' in kwargs:
        url += "&taskId=%s"%kwargs['task_id']
    elif 'batch_id' in kwargs:
        url += "&batchId=%s"%kwargs['batch_id']

    res = requests.get(url, headers = header1)
    assert res.status_code==200, "获取审核/批量审核任务详情失败, %s"%res.text

    total = jsonpath(res.json(), '$.total')[0]
    assert total, f'任务的明细为空，任务id：{kwargs}'

    return res.json()

def get_file_element_from_task_detail(res_json):
    """
    获取详情页的文件和元素，兼容审核任务、批量审核、维护任务
    :param res_json: 任务详情的接口返回内容（json格式）
    :return: 任务详情中，所有文件id和元素id组成的列表
    """
    __init()
    detail_list = []

    # 以文件和元素为维度组合成列表
    file_list = jsonpath(res_json, '$.data.[fileId]')
    element_list = jsonpath(res_json, '$.data.[elementId]')
    for i in range(len(element_list)):
        detail_list.append((file_list[i], element_list[i]))

    return detail_list

def get_maintain_task_detail(task_id):
    """获取维护任务详情

    :param task_id:

    :return:
    """

    __init()
    url = url1 + "/api/v1/MaintainBatch/filter/paging?taskId=%s&pageIndex=0&pageSize=10000"%task_id

    res = requests.get(url, headers=header1)
    assert res.status_code == 200, "获取维护任务详情失败, %s" % res.text

    total = jsonpath(res.json(), '$.total')[0]
    assert total, f'任务的明细为空，任务id：{task_id}'

    return res.json()